﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DataAccess
{
    public class MenuDAO
    {
        /**
         * Load menu list which is available from db.
         */
        public DataSet loadMenu()
        {
            DataSet ds = new DataSet();
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                SqlDataAdapter objDA = new SqlDataAdapter("pr_getAllMenu", con);
                objDA.SelectCommand.CommandType = CommandType.StoredProcedure;
                objDA.Fill(ds, "tb_menu");
                return ds;
            }
            catch (Exception e1)
            {
                throw e1;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        // Judge whether this food is available
        public bool isFoodAvailable(string strFoodID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = string.Format("select COUNT(foodID) from tb_menu where foodID='{0}' and foodState=1;", strFoodID);
                SqlCommand command = new SqlCommand(strSql, con);

                // call ExecuteReader() to get a data reader
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    if (Int32.Parse(sdr[0].ToString()) == 1)
                    {
                        sdr.Close();
                        return true;
                    }
                }
                sdr.Close();
                return false;
            }
            catch (Exception e1)
            {
                throw e1;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        /* Set food state whether is isAvail or not */
        public void setFoodState(string strOperName, string foodID, bool isAvail)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = string.Format("update tb_menu set foodState={0} where foodID='{1}';", (isAvail ? "1" : "0"), foodID);
                SqlCommand command = new SqlCommand(strSql, con);

                // call ExecuteReader() to get a data reader
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    if (Int32.Parse(sdr[0].ToString()) == 1)
                    {
                        sdr.Close();
                    }
                }
                sdr.Close();
            }
            catch (Exception e1)
            {
                throw e1;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        public Object getFood(string strFoodID)
        {
            SqlConnection con = null;
            SqlDataReader sdr = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = String.Format("select COUNT(foodID) from tb_menu where foodID='{0}';", strFoodID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    if (Int32.Parse(sdr[0].ToString()) < 1)
                    {
                        throw new Exception("该菜品不存在");
                    }
                }
                sdr.Close();
                sdr.Dispose();
                sdr = null;
                strSql = String.Format("select * from tb_menu where foodID='{0}';", strFoodID);
                cmd = new SqlCommand(strSql, con);
                sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    Hashtable ht = new Hashtable();
                    ht.Add("foodID", sdr["foodID"]);
                    ht.Add("foodName", sdr["foodName"]);
                    ht.Add("foodType", sdr["foodType"]);
                    ht.Add("foodUnit", sdr["foodUnit"]);
                    ht.Add("price", sdr["price"]);
                    ht.Add("discount", sdr["discount"]);
                    ht.Add("intro", sdr["intro"]);
                    return ht;
                }
                sdr.Close();
                return null;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (sdr != null) { sdr.Close(); sdr.Dispose(); sdr = null; };
                if (con != null) { con.Close(); con.Dispose(); con = null; };
            }
        }

        public bool isFoodExist(string strFoodID)
        {
            SqlConnection con = null;
            SqlDataReader sdr = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = String.Format("select COUNT(foodID) from tb_menu where foodID='{0}';", strFoodID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    if (Int32.Parse(sdr[0].ToString()) > 0)
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (sdr != null) { sdr.Close(); sdr.Dispose(); sdr = null; };
                if (con != null) { con.Close(); con.Dispose(); con = null; };
            }
        }

        public void addOrUpdateFood(string strOperName, string strID, string strName, string strType, string strUnit, double price, float discount, string intro)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                SqlCommand cmd = new SqlCommand("pr_addOrUpdateFood", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] sparams = {
                                             new SqlParameter("@foodID", SqlDbType.VarChar, 8),
                                             new SqlParameter("@foodName", SqlDbType.VarChar, 20),
                                             new SqlParameter("@foodType", SqlDbType.VarChar, 10),
                                             new SqlParameter("@foodUnit", SqlDbType.VarChar, 4),
                                             new SqlParameter("@price", SqlDbType.Decimal),
                                             new SqlParameter("@discount", SqlDbType.Float),
                                             new SqlParameter("@intro", SqlDbType.VarChar, 40)
                                         };
                sparams[0].Value = strID;
                sparams[1].Value = strName;
                sparams[2].Value = strType;
                sparams[3].Value = strUnit;
                //sparams[4].Value = price;
                sparams[4].Value = 999.99d;
                sparams[5].Value = discount;
                sparams[6].Value = intro;
                // Add parameters
                foreach (SqlParameter param in sparams)
                {
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();

                string strDetails = "添加/更新菜品: " + strID + ", 名称: " + strName + "成功";
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                string strDetails = "添加/更新菜品: " + strID + ", 名称: " + strName + "失败";
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e;
            }
            finally
            {
                if (con != null) { con.Close(); con.Dispose(); con = null; };
            }
        }

        /* Remove food item from menu */
        public void removeFood(string strOperName, string strFoodID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = string.Format("delete from tb_menu where foodID='{0}';", strFoodID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                string strDetails = "删除菜品: " + strFoodID + "成功";
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
            }
            catch (Exception e1)
            {
                string strDetails = "删除菜品: " + strFoodID + "失败";
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e1;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }
    }
}
